package com.supergdtop.www.run.SQLMethod;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.supergdtop.www.run.Vo.User;
import com.supergdtop.www.run.tools.DbOpenhelper;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by chn10 on 2016/10/23.
 */

public class SQLUserService {
    private DbOpenhelper dbOpenHelper;

    public SQLUserService(Context context) {
        dbOpenHelper = new DbOpenhelper(context);
    }

    /**
     * 添加User
     *
     * @param User
     */
    public void addUser(User User) {
        // 对读和写操作的方法  
        // 如果当我们二次调用这个数据库方法,他们调用的是同一个数据库对象,在这里的方法创建的数据调用对象是用的同一个对象  
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        db.execSQL("insert into User(name,amount) values(?,?,?)", new Object[]
                {User.getName(), User.getPhonenumber(),User.getPwd()});
    }

    /**
     * 修改User
     *
     * @param User
     */
    public void modifyUser(User User) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        db.execSQL("update User set name=? where Userid=?", new Object[]
                {User.getName(), User.getId()});
    }

    /**
     * 删除User
     *
     * @param User
     */
    public void deleteUser(Integer id) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        db.execSQL("delete from User where Userid=?", new Object[]
                {id.toString()});
    }

    /**
     * 根据User的Id查询User对象
     *
     * @param id User的ID
     * @return User
     */
    public User findUser(Integer id) {
        // 只对读的操作的方法  
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        // Cursor游标的位置,默认是0,所有在操作时一定要先cursor.moveToFirst()一下,定位到第一条记录  
        // Cursor cursor =  
        // db.rawQuery("select * from User Where Userid=?",new  
        // String[]{id.toString()});  
        Cursor cursor = db.query("User", new String[]
                {"Userid", "name", "amount"}, "Userid=?", new String[]
                {id.toString()}, null, null, null);
        if (cursor.moveToFirst()) {
            int UserId = cursor.getInt(cursor.getColumnIndex("Userid"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            int amount = cursor.getInt(cursor.getColumnIndex("amount"));
            return new User(UserId, name, amount);
        }
        return null;
    }

    /**
     * 返回User对象的集合
     *
     * @return List<User>
     */
    public List<User> findUserList(Integer start, Integer length) {
        List<User> Users = new ArrayList<User>();
        // 只对读的操作的方法  
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from User limit ?,?",
                new String[]
                        {start.toString(), length.toString()});
        cursor = db.query("User", null, null, null, null, null, null, start
                + "," + length);
        while (cursor.moveToNext()) {
            int UserId = cursor.getInt(cursor.getColumnIndex("Userid"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            int amount = cursor.getInt(cursor.getColumnIndex("amount"));
            Users.add(new User(UserId, name, amount));
        }
        return Users;
    }

    /**
     * 返回User的记录总个数
     *
     * @return
     */
    public Long getCount() {
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select count(0) from User ", null);
        // 这里必定有一条记录.所有不用判断,直接移到第一条.  
        cursor.moveToFirst();
        // 这里只有一个字段时候 返回  
        return cursor.getLong(0);
    }

    /**
     * 操作一个事务
     *
     * @return
     */
    public String getTransaction() {
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        String success = "";
        db.beginTransaction();
        try {
            db.execSQL("update User set amount = amount+10 where UserId=?",
                    new Object[]
                            {1});
            db.execSQL("update User set amount = amount-10 where UserId=?",
                    new Object[]
                            {2});
            success = "success";
        } catch (Exception e) {
            success = "input";
        } finally {
            // db.setTransactionSuccessful();//设置事务标志为成功,当结束事务时就会提交事务  
            db.endTransaction();
        }
        return success;
    }
}  
